package com.kyleromano.disgaea.sql;

import com.kyleromano.disgaea.R;
import com.kyleromano.disgaea.dataType.Classes;
import com.kyleromano.disgaea.dataType.ParentJob;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
	private Context context;
	private SQLiteDatabase db;
	
	/**
	 * DBHelper Constructor
	 */
	public DBHelper(Context context)
	{
		super(context, DBConstants.DBNAME, null, DBConstants.DBVER);
		this.context = context;
	}
	
	/**
	 * Database function called when reopening an existing database
	 */
	@Override
	public void onOpen(SQLiteDatabase db) {
		if(DBConstants.CLEAR_DB_ON_START)
			this.onUpgrade(db, DBConstants.DBVER, DBConstants.DBVER);
		this.db = db;
	}

	/**
	 * Database creation called if the database does not exist
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// Jobs Table
		db.execSQL(
			"CREATE TABLE " +DBConstants.TJOBS+ "(" +DBConstants.CJOBID+ " INTEGER PRIMARY KEY,"+
													 DBConstants.CJOBNAME+ " TEXT);");
		// Classes Table
		db.execSQL(
			"CREATE TABLE " +DBConstants.TCLASSES+ "(" +DBConstants.CCLASSID+ " INTEGER PRIMARY KEY,"+
														DBConstants.CCLASSNAME+ " TEXT," +
														DBConstants.CCLASSUNLOCKED+" INTEGER DEFAULT 0);");
		// Characters Table
		db.execSQL(
			"CREATE TABLE " +DBConstants.TCHARACTERS+ "(" +DBConstants.CCID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+
														   DBConstants.CCHARNAME+ " TEXT, "+
														   DBConstants.CLEVEL+ " INTEGER, " +
														   DBConstants.CCLASSID+ " INTEGER NOT NULL, " +
														   DBConstants.CJOBID+ " INTEGER NOT NULL);");
		// Current Chapter Table
		db.execSQL(
				"CREATE TABLE " +DBConstants.TCHAPTER+ "(" +DBConstants.CCURRENTCHAPTER+ " INTEGER PRIMARY KEY);");
		db.execSQL("INSERT INTO " +DBConstants.TCHAPTER+ "(" + DBConstants.CCURRENTCHAPTER+") VALUES (1);");
		// System Variables Table
		db.execSQL("CREATE TABLE " + DBConstants.TSYSDATA + "(" + DBConstants.CSORTORDER + " INTEGER NOT NULL, " +
																  DBConstants.CASCENDING + " INTEGER NOT NULL);");
		//Character View
		db.execSQL(
			"CREATE VIEW " + DBConstants.VCHARACTER + " AS " +
			"SELECT c1." +DBConstants.CCID+ ", c1." +DBConstants.CCHARNAME+ ", c1." +DBConstants.CLEVEL+ ", c2."+DBConstants.CCLASSID+ ", c2." +DBConstants.CCLASSNAME+ ", j."+DBConstants.CJOBID+ ", j." +DBConstants.CJOBNAME+ " " + 
			"FROM " +DBConstants.TCHARACTERS+ " as c1 " +
			"INNER JOIN " +DBConstants.TCLASSES+ " as c2 " +
			"ON c1." +DBConstants.CCLASSID+ " = c2." +DBConstants.CCLASSID+ " " + 
			"INNER JOIN " +DBConstants.TJOBS+ " as j " +
			"ON c1." +DBConstants.CJOBID+ " = j." +DBConstants.CJOBID+ ";");
		//Class Unlock View
		db.execSQL("CREATE VIEW " + DBConstants.VCLASS + " AS " +
				"SELECT " +DBConstants.CCLASSID+ " AS _id, " +DBConstants.CCLASSUNLOCKED+ " FROM " + DBConstants.TCLASSES+ ";");
		
		// Pre-Populate tables
		ContentValues cv = new ContentValues();
		// System Variables
		cv.put(DBConstants.CASCENDING,0);
		cv.put(DBConstants.CSORTORDER,0);
		db.insert(DBConstants.TSYSDATA, null, cv);		
		// Jobs and Classes
		cv = new ContentValues();
		cv.put(DBConstants.CJOBID, ParentJob.WARRIOR);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sWarrior));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.VALKYRIE);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sValkyrie));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.WITCH);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sWitch));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.MAGICIAN);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sMagician));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.HEALER);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sHealer));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.ARCHER);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sArcher));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.GUNNER);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sGunner));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.ARMORKNIGHT);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sArmorKnight));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.THIEF);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sThief));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.NINJA);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sNinja));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.SAMURAI);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sSamurai));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.BEASTMASTER);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sBeastmaster));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.SORCERER);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sSorcerer));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.BOUNCER);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sBouncer));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.PROFESSOR);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sProfessor));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.MAGICKNIGHT);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sMagicKnight));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.MASKEDHERO);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sMaskedHero));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.ONMYOMONK);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sOnmyoMonk));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.BATTLESUIT);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sBattlesuit));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.ANDROID);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sAndroid));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.SLUDGE);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sSludge));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.ORC);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sOrc));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.MOTHMAN);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sMothman));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.SHROOM);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sShroom));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.GHOST);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sGhost));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.SLUMBERCAT);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sSlumbercat));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.PRINNY);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sPrinny));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.ZOMBIE);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sZombie));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.GARGOYLE);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sGargoyle));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.FLORABEAST);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sFlorabeast));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.ROC);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sRoc));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.REAPER);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sReaper));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.MYSTICBEAST);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sMysticbeast));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.SUCCUBUS);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sSuccubus));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.DRAGONZOMBIE);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sDragonZombie));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.WOODGOLEM);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sWoodGolem));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.DRAGON);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sDragon));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.FELYNN);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sFelynn));
		db.insert(DBConstants.TJOBS, null, cv);
		cv.put(DBConstants.CJOBID, ParentJob.RIFLEDEMON);
		cv.put(DBConstants.CJOBNAME, context.getString(R.string.sRifledemon));
		db.insert(DBConstants.TJOBS, null, cv);
		
		classLoop(context.getResources().getStringArray(R.array.maleFighter),ParentJob.WARRIOR, db);
		classLoop(context.getResources().getStringArray(R.array.femaleFighter),ParentJob.VALKYRIE, db);
		classLoop(context.getResources().getStringArray(R.array.mage),ParentJob.WITCH, db);
		classLoop(context.getResources().getStringArray(R.array.skull),ParentJob.MAGICIAN, db);
		classLoop(context.getResources().getStringArray(R.array.healer),ParentJob.HEALER, db);
		classLoop(context.getResources().getStringArray(R.array.archer),ParentJob.ARCHER, db);
		classLoop(context.getResources().getStringArray(R.array.gunner),ParentJob.GUNNER, db);
		classLoop(context.getResources().getStringArray(R.array.armorKnight),ParentJob.ARMORKNIGHT, db);
		classLoop(context.getResources().getStringArray(R.array.thief),ParentJob.THIEF, db);
		classLoop(context.getResources().getStringArray(R.array.ninja),ParentJob.NINJA, db);
		classLoop(context.getResources().getStringArray(R.array.samurai),ParentJob.SAMURAI, db);
		classLoop(context.getResources().getStringArray(R.array.beastMaster),ParentJob.BEASTMASTER, db);
		classLoop(context.getResources().getStringArray(R.array.sorcerer),ParentJob.SORCERER, db);
		classLoop(context.getResources().getStringArray(R.array.bouncer),ParentJob.BOUNCER, db);
		classLoop(context.getResources().getStringArray(R.array.professor),ParentJob.PROFESSOR, db);
		classLoop(context.getResources().getStringArray(R.array.magicKnight),ParentJob.MAGICKNIGHT, db);
		classLoop(context.getResources().getStringArray(R.array.maskedHero),ParentJob.MASKEDHERO, db);
		classLoop(context.getResources().getStringArray(R.array.onmyoMonk),ParentJob.ONMYOMONK, db);
		classLoop(context.getResources().getStringArray(R.array.battleSuit),ParentJob.BATTLESUIT, db);
		classLoop(context.getResources().getStringArray(R.array.android),ParentJob.ANDROID, db);
		classLoop(context.getResources().getStringArray(R.array.sludge),ParentJob.SLUDGE, db);
		classLoop(context.getResources().getStringArray(R.array.orc),ParentJob.ORC, db);
		classLoop(context.getResources().getStringArray(R.array.mothman),ParentJob.MOTHMAN, db);
		classLoop(context.getResources().getStringArray(R.array.shroom),ParentJob.SHROOM, db);
		classLoop(context.getResources().getStringArray(R.array.ghost),ParentJob.GHOST, db);
		classLoop(context.getResources().getStringArray(R.array.slumberCat),ParentJob.SLUMBERCAT, db);
		classLoop(context.getResources().getStringArray(R.array.prinny),ParentJob.PRINNY, db);
		classLoop(context.getResources().getStringArray(R.array.zombie),ParentJob.ZOMBIE, db);
		classLoop(context.getResources().getStringArray(R.array.gargoyle),ParentJob.GARGOYLE, db);
		classLoop(context.getResources().getStringArray(R.array.floraBeast),ParentJob.FLORABEAST, db);
		classLoop(context.getResources().getStringArray(R.array.roc),ParentJob.ROC, db);
		classLoop(context.getResources().getStringArray(R.array.reaper),ParentJob.REAPER, db);
		classLoop(context.getResources().getStringArray(R.array.mysticBeast),ParentJob.MYSTICBEAST, db);
		classLoop(context.getResources().getStringArray(R.array.succubus),ParentJob.SUCCUBUS, db);
		classLoop(context.getResources().getStringArray(R.array.dragonZombie),ParentJob.DRAGONZOMBIE, db);
		classLoop(context.getResources().getStringArray(R.array.woodGolem),ParentJob.WOODGOLEM, db);
		classLoop(context.getResources().getStringArray(R.array.dragon),ParentJob.DRAGON, db);
		classLoop(context.getResources().getStringArray(R.array.felynn),ParentJob.FELYNN, db);
		classLoop(context.getResources().getStringArray(R.array.rifleDemon),ParentJob.RIFLEDEMON, db);
		unlockDefaultClasses(db);
	}

	/**
	 * Database upgrade called if the version number changes
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//Log the version change
		Log.w(DBHelper.class.getName(), "Upgrading Database from " + oldVersion + " to " + newVersion);
		
		//Relies on fallthrough to update the database from initial version to the final version if possible
		switch(oldVersion) {
		//Update from Version 1 of the database
		case 1:
			db.execSQL("ALTER TABLE " +DBConstants.TCLASSES+ " ADD COLUMN " +DBConstants.CCLASSUNLOCKED+ " INTEGER DEFAULT 0;");
			//Create new Class View
			db.execSQL("CREATE VIEW " + DBConstants.VCLASS + " AS " +
					"SELECT " +DBConstants.CCLASSID+ " AS _id, " +DBConstants.CCLASSUNLOCKED+ " FROM " + DBConstants.TCLASSES+ ";");
			unlockDefaultClasses(db);
		case 2:
			// Current Chapter Table
			db.execSQL(
					"CREATE TABLE IF NOT EXISTS " +DBConstants.TCHAPTER+ "(" +DBConstants.CCURRENTCHAPTER+ " INTEGER PRIMARY KEY);");
			db.execSQL("INSERT INTO " +DBConstants.TCHAPTER+ "(" + DBConstants.CCURRENTCHAPTER+") VALUES (1);");
		case 3:
			// System Variables Table
			db.execSQL("CREATE TABLE IF NOT EXISTS " + DBConstants.TSYSDATA + "(" + DBConstants.CSORTORDER + " INTEGER NOT NULL, " +
																	  DBConstants.CASCENDING + " INTEGER NOT NULL);");
			ContentValues cv = new ContentValues();
			// System Variables
			cv.put(DBConstants.CASCENDING,0);
			cv.put(DBConstants.CSORTORDER,0);
			db.insert(DBConstants.TSYSDATA, null, cv);	
		}
	}
	
	/**
	 * Updates the records for the default classes available when starting a new game from scratch
	 */
	private void unlockDefaultClasses(SQLiteDatabase db)
	{
		int[] unlockClasses = new int[] {
			Classes.FIGHTER,		//1
			Classes.LADYFIGHTER,	//2
			Classes.REDSKULL,		//3
			Classes.GREENSKULL,		//4
			Classes.BLUESKULL,		//5
			Classes.REDMAGE,		//6
			Classes.GREENMAGE,		//7
			Classes.BLUEMAGE,		//8
			Classes.HEALER,			//9
			Classes.THIEF,			//10
			Classes.GREENSLIME,		//11
			Classes.PETITEORC,		//12
			Classes.MOTHMAN,		//13
			Classes.ERYNGI,			//14
			Classes.GHOST,			//15
			Classes.CATSABER,		//16
			Classes.PRIVATEPRINNY,	//17
			Classes.ZOMBIE,			//18
			Classes.GARGOYLE,		//19
			Classes.ALRAUNE,		//20
			Classes.COCKATRICE,		//21
			Classes.CUSITH			//22
		};
		ContentValues cv = new ContentValues();
		cv.put(DBConstants.CCLASSUNLOCKED, "1");
		int i = 0;
		while(i < 22) {
			db.update(DBConstants.TCLASSES, cv, DBConstants.CCLASSID+"="+unlockClasses[i],null);
			i++;
		}
	}
	
	/**
	 * Iterate over a collection to populate the database with appropriate names
	 * @param classNames Array of Strings representing the Class names
	 * @param classID The numeric ID identifying each String name
	 * @param db The database object being manipulated to build this entry
	 */
	private void classLoop(String[] classNames, int classID, SQLiteDatabase db)
	{
		ContentValues cv = new ContentValues();
		for(int i = 0; i < 6; i++)
		{
			cv.put(DBConstants.CCLASSID, classID + i);
			cv.put(DBConstants.CCLASSNAME, classNames[i]);
			db.insert(DBConstants.TCLASSES, null, cv);
		}
	}
	
	public boolean isOpen() {
		return db.isOpen();
	}
}
